package hr.riteh.assistivecommunicator;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database
{
	class OpenHelper extends SQLiteOpenHelper
	{
		// Database schema version
		private static final int DATABASE_VERSION = 1;

		// Database name
		private static final String DATABASE_NAME = "Communicator.db";

		// Table name constants
		private static final String TABLE_CATEGORY = "Category";
		private static final String TABLE_SENTENCE = "Sentence";

		// Field name constants
		private static final String FIELDNAME_ID         = "ID";
		private static final String FIELDNAME_NAME       = "Name";
		private static final String FIELDNAME_CONTENT    = "Content";
		private static final String FIELDNAME_IMAGENAME  = "ImageName";
		private static final String FIELDNAME_USAGE      = "Usage";
		private static final String FIELDNAME_CATEGORYID = "CategoryID";

		// Field definition constants
		private static final String FIELD_ID         = FIELDNAME_ID         + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT";
		private static final String FIELD_NAME       = FIELDNAME_NAME       + " TEXT NOT NULL";
		private static final String FIELD_CONTENT    = FIELDNAME_CONTENT    + " TEXT NOT NULL";
		private static final String FIELD_IMAGENAME  = FIELDNAME_IMAGENAME  + " TEXT NOT NULL";
		private static final String FIELD_USAGE      = FIELDNAME_USAGE      + " INTEGER NOT NULL DEFAULT 0";
		private static final String FIELD_CATEGORYID = FIELDNAME_CATEGORYID + " INTEGER NOT NULL";

		// SQL queries
		private static final String QUERY_CREATE_CATEGORY = "CREATE TABLE " + TABLE_CATEGORY + " (" + FIELD_ID + ", " + FIELD_NAME + ", " + FIELD_IMAGENAME + ", " + FIELD_USAGE + ")";
		private static final String QUERY_CREATE_SENTENCE = "CREATE TABLE " + TABLE_SENTENCE + " (" + FIELD_ID + ", " + FIELD_CATEGORYID + ", " + FIELD_CONTENT + ", " + FIELD_USAGE + ")";

		public OpenHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(QUERY_CREATE_CATEGORY);
			db.execSQL(QUERY_CREATE_SENTENCE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
		}

		public void increment(String table, String fieldName, String whereFieldName, String whereFieldValue)
		{
			SQLiteDatabase database = mOpenHelper.getWritableDatabase();
			database.execSQL(String.format("UPDATE %s SET %s = %s + 1 WHERE %s = %s", table, fieldName, fieldName, whereFieldName, whereFieldValue));
			database.close();
		}
	}

	private OpenHelper mOpenHelper;

	public Database(Context context)
	{
		mOpenHelper = new OpenHelper(context);
	}

	public List<Category> getCategories()
	{
		List<Category> categories = new ArrayList<Category>();
		String fields [] = { OpenHelper.FIELDNAME_ID, OpenHelper.FIELDNAME_NAME, OpenHelper.FIELDNAME_IMAGENAME, OpenHelper.FIELDNAME_USAGE };
		SQLiteDatabase database = mOpenHelper.getReadableDatabase();
		Cursor cursor = database.query(OpenHelper.TABLE_CATEGORY, fields, null, null, null, null, OpenHelper.FIELDNAME_USAGE + " DESC");
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			categories.add(new Category(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3)));
			cursor.moveToNext();
		}

		cursor.close();
		database.close();

		return categories;
	}

	public List<Sentence> getCategorySentences(long category_id)
	{
		List<Sentence> sentences = new ArrayList<Sentence>();
		String columns [] = { OpenHelper.FIELDNAME_ID, OpenHelper.FIELDNAME_CONTENT, OpenHelper.FIELDNAME_USAGE };
		String selection_values [] = { String.valueOf(category_id) };
		SQLiteDatabase database = mOpenHelper.getReadableDatabase();
		Cursor cursor = database.query(OpenHelper.TABLE_SENTENCE, columns, OpenHelper.FIELDNAME_CATEGORYID + " = ?", selection_values, null, null, OpenHelper.FIELDNAME_USAGE + " DESC");
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			sentences.add(new Sentence(cursor.getLong(0), cursor.getString(1), cursor.getInt(2)));
			cursor.moveToNext();
		}

		cursor.close();
		database.close();

		return sentences;
	}

	public void putCategories(List<Category> categories)
	{
	}

	public void putSentences(List<Sentence> sentences)
	{
	}

	public void increaseCategoryUsage(long category_id)
	{
		mOpenHelper.increment(OpenHelper.TABLE_CATEGORY, OpenHelper.FIELDNAME_USAGE, OpenHelper.FIELDNAME_ID, String.valueOf(category_id));
	}

	public void increaseSentenceUsage(long sentence_id)
	{
		mOpenHelper.increment(OpenHelper.TABLE_SENTENCE, OpenHelper.FIELDNAME_USAGE, OpenHelper.FIELDNAME_ID, String.valueOf(sentence_id));
	}
}
